﻿using Dapper;
using DbFactory.Base;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;
using MySql.Data.MySqlClient;
using Mysqlx.Crud;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using static System.Runtime.InteropServices.Marshalling.IIUnknownCacheStrategy;

namespace DbFactory
{
    public class MySQLConnection : IDatabaseConnection
    {
        private MySqlConnection _connection;

        public void connect()
        {
            IConfiguration config = new ConfigurationBuilder()
            .Add(new JsonConfigurationSource { Path = "app.json", ReloadOnChange = true })
            .Build();
            string connectionString = config.GetSection("connectionStr").Value;
            _connection = new MySqlConnection(connectionString);
            _connection.Open();
        }

        public void connect(string connectionString)
        {
            _connection = new MySqlConnection(connectionString);
            _connection.Open();
        }


        public void disconnect()
        {
            _connection.Close();
            _connection.Dispose();
        }



        public ConnectionState connectState()
        {
            return _connection.State;
        }



        public DbTransaction BeginTransaction()
        {
            return _connection.BeginTransaction();
        }

        public void Execute(string sql, object param=null,IDbTransaction transaction=null)
        {
            _connection.Execute(sql,param, transaction);
        }



        public void insert<T>(T entity, DbTransaction transaction = null)
        {
            var type = typeof(T);
            var properties = type.GetProperties()
                .Where(p => p.CanWrite && p.PropertyType.IsPublic)
                .ToList();
            var columnNames = string.Join(", ", properties.Select(p => p.Name));
            var parameterNames = string.Join(", ", properties.Select(p => $"@{p.Name}"));
            var sql = $"INSERT INTO {type.Name} ({columnNames}) VALUES ({parameterNames})";
            var parameters = new DynamicParameters();
            foreach (var property in properties)
            {
                parameters.Add("@" + property.Name, property.GetValue(entity, null));
            }
            if (transaction != null)
            {
                _connection .Execute(sql, parameters, transaction);
            }
            else
            {
                _connection.Execute(sql, parameters);
            }
        }

        public async Task insertAsync<T>(T entity, DbTransaction transaction = null)
        {
            var type = typeof(T);
            var properties = type.GetProperties()
                .Where(p => p.CanWrite && p.PropertyType.IsPublic)
                .ToList();
            var columnNames = string.Join(", ", properties.Select(p => p.Name));
            var parameterNames = string.Join(", ", properties.Select(p => $"@{p.Name}"));
            var sql = $"INSERT INTO {type.Name} ({columnNames}) VALUES ({parameterNames})";
            var parameters = new DynamicParameters();
            foreach (var property in properties)
            {
                parameters.Add("@" + property.Name, property.GetValue(entity, null));
            }
            if (transaction != null)
            {
                await _connection.ExecuteAsync(sql, parameters, transaction);
            }
            else
            {
                await _connection.ExecuteAsync(sql, parameters);
            }
        }



        public void update<T>(T entity, DbTransaction transaction = null, string key="Id") 
        {
            var type = typeof(T);
            var properties = type.GetProperties()
                .Where(p => p.CanRead && p.PropertyType.IsPublic)
                .ToList();

            var setClause = string.Join(", ", properties
                .Where(p => !p.Name.Equals("Id", StringComparison.OrdinalIgnoreCase))
                .Select(p => $"{p.Name} = @{p.Name}"));
            var whereClause = key + " = @"+ key;
            var sql = $"UPDATE {type.Name} SET {setClause} WHERE {whereClause}";

            var parameters = new DynamicParameters();
            foreach (var property in properties)
            {
                parameters.Add("@" + property.Name, property.GetValue(entity, null));
            }
            if (transaction != null)
            {
                _connection.Execute(sql, parameters, transaction);
            }
            else
            {
                _connection.Execute(sql, parameters);
            }
        }


        public void delete<T>(string id, DbTransaction transaction = null)
        {
            string keyName = "id";
            var t = typeof(T);
            string sql = string.Format("delete from {0} where {1}={2}", t.Name, keyName, id);
            if (transaction != null)
            {
                _connection.Execute(sql, transaction);
            }
            else
            {
                _connection.Execute(sql);
            }

        }

        public void delete<T>(string id, string keyName="id" , DbTransaction transaction = null)
        {
            var t = typeof(T);
            string sql = string.Format("delete from {0} where {1}='{2}'", t.Name,keyName,id);
            if (transaction != null)
            {
                _connection.Execute(sql,transaction);
            }
            else
            {
                _connection.Execute(sql);
            }
            
        }

        public void delete<T>(string id)
        {
            string keyName = "id";
            var t = typeof(T);
            string sql = string.Format("delete from {0} where {1}='{2}'", t.Name, keyName, id);
            _connection.Execute(sql);
        }


        public void delete<T>(Expression<Func<T, bool>> expression, DbTransaction transaction = null)
        {
            var t = typeof(T);
            var where = expression.ToSqlWhere();
            string sql = string.Format("delete from {0} where 1=1 {1}", t.Name, where);
            if (transaction != null)
            {
                _connection.Execute(sql,transaction);
            }
            else
            {
                _connection.Execute(sql);
            }
        }


        public IEnumerable<T> QueryList<T>()
        {
            var type = typeof(T);
            string sql = string.Format("select * from {0}",type.Name);
            return _connection.Query<T>(sql);
        }

        public IEnumerable<T> QueryList<T>(int pageSize,int page, string orderby = "")
        {
            int skip = pageSize * (page - 1);
            var type = typeof(T);
            string sql = string.Format("select * from {0} where 1=1 {1} LIMIT {2},{3}", type.Name, orderby, skip, pageSize);
            return _connection.Query<T>(sql);
        }

        public IEnumerable<T> QueryList<T>(string where)
        {
            var type = typeof(T);
            string sql = string.Format("select * from {0} where 1=1 {1}",type.Name,where);
            return _connection.Query<T>(sql);
        }

        public IEnumerable<T> QueryList<T>(string where, int pageSize, int page)
        {
            int skip = pageSize * (page - 1);
            var type = typeof(T);
            string sql = string.Format("select * from {0} where 1=1 {1} LIMIT {2},{3}", type.Name, where,skip,pageSize);
            return _connection.Query<T>(sql);
        }

        public T QueryFirst<T>(string id,string keyName="id")
        {
            var type = typeof(T);
            string sql = string.Format("select * from {0} where {1}=@id",type.Name,keyName);
            var parameters = new DynamicParameters();
            parameters.Add("@id", id);
            return _connection.QueryFirstOrDefault<T>(sql, parameters);
        }


        public T QueryFirstWhere<T>(Expression<Func<T, bool>> expression)
        {
            var where = expression.ToSqlWhere();
            return QueryFirstWhere<T>(where);
        }

        public T QueryFirstWhere<T>(string where)
        {
            var type = typeof(T);
            string sql = string.Format("select * from {0} where 1=1 {1}",type.Name,where);
            return _connection.QueryFirstOrDefault<T>(sql);
        }

        public IEnumerable<T> QuerySql<T>(string sql, object param = null)
        {
            return _connection.Query<T>(sql, param);
        }


        public T QuerySqlOne<T>(string sql, object param = null)
        {
            return _connection.ExecuteScalar<T>(sql, param);
        }


        public IEnumerable<T> QuerySql<T>(string sql, int pageSize, int page, object param = null)
        {
            int skip = pageSize * (page - 1);
            string querySql = string.Format(@"{0} LIMIT {1},{2}", sql, skip, pageSize);
            return _connection.Query<T>(querySql, param);
        }


        public List<string> GetTbNameList()
        {
            var tableNames = new List<string>();
            using (var cmd = new MySqlCommand("SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()", _connection))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        tableNames.Add(reader["table_name"].ToString());
                    }
                }
            }
            return tableNames;
        }



        public Base.TableInfo GetTableInfo(string tableName)
        {
            var tableInfo = new Base.TableInfo { TableName = tableName, Columns = new List<ColumnInfo>() };
            // 获取表注释
            using (var cmd = new MySqlCommand(
                "SELECT table_comment FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = @tableName", _connection))
            {
                cmd.Parameters.AddWithValue("@tableName", tableName);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        tableInfo.TableDesc = reader["table_comment"].ToString();
                    }
                }
            }
            // 获取字段信息
            using (var cmd = new MySqlCommand(
                @"SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT 
              FROM information_schema.columns 
              WHERE table_schema = DATABASE() AND table_name = @tableName", _connection))
            {
                cmd.Parameters.AddWithValue("@tableName", tableName);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        tableInfo.Columns.Add(new ColumnInfo
                        {
                            ColumnName = reader["COLUMN_NAME"].ToString(),
                            DataType = reader["COLUMN_TYPE"].ToString(),
                            ColumnDesc = reader["COLUMN_COMMENT"].ToString()
                        });
                    }
                }
            }
            return tableInfo;
        }



        public void CreateTable<T>()
        {
            var type = typeof(T);
            var properties = type.GetProperties();
            var columns = new List<string>();
            var primaryKeys = new List<string>();

            foreach (var property in properties)
            {
                var columnDefinition = new StringBuilder();
                columnDefinition.Append($"`{property.Name}` ");

                // Map C# types to MySQL types
                if (property.PropertyType == typeof(string))
                {
                    columnDefinition.Append("VARCHAR(255)");
                }
                else if (property.PropertyType == typeof(int) || property.PropertyType == typeof(int?))
                {
                    columnDefinition.Append("INT");
                }
                else if (property.PropertyType == typeof(long) || property.PropertyType == typeof(long?))
                {
                    columnDefinition.Append("BIGINT");
                }
                else if (property.PropertyType == typeof(decimal) || property.PropertyType == typeof(decimal?))
                {
                    columnDefinition.Append("DECIMAL(18,2)");
                }
                else if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
                {
                    columnDefinition.Append("DATETIME");
                }
                else if (property.PropertyType == typeof(bool) || property.PropertyType == typeof(bool?))
                {
                    columnDefinition.Append("TINYINT(1)");
                }
                else if (property.PropertyType == typeof(float) || property.PropertyType == typeof(float?))
                {
                    columnDefinition.Append("FLOAT");
                }
                else if (property.PropertyType == typeof(double) || property.PropertyType == typeof(double?))
                {
                    columnDefinition.Append("DOUBLE");
                }
                else if (property.PropertyType == typeof(byte[]))
                {
                    columnDefinition.Append("LONGBLOB");
                }
                else
                {
                    columnDefinition.Append("VARCHAR(255)");
                }

                // Add character set and collation for string types
                if (property.PropertyType == typeof(string))
                {
                    columnDefinition.Append(" CHARACTER SET utf8 COLLATE utf8_general_ci");
                }

                // Check if property is primary key
                bool isPrimaryKey = property.Name.Equals("Id", StringComparison.OrdinalIgnoreCase);
                
                // Primary key columns must be NOT NULL
                if (isPrimaryKey)
                {
                    columnDefinition.Append(" NOT NULL");
                    primaryKeys.Add($"`{property.Name}`");
                }
                else
                {
                    // For non-primary key columns, handle nullability based on type
                    if (property.PropertyType.IsValueType && Nullable.GetUnderlyingType(property.PropertyType) == null)
                    {
                        columnDefinition.Append(" NOT NULL");
                    }
                    else
                    {
                        columnDefinition.Append(" NULL");
                    }
                }

                columns.Add(columnDefinition.ToString());
            }

            // Build the final SQL statement
            var sql = new StringBuilder();
            sql.AppendLine($"CREATE TABLE IF NOT EXISTS `{type.Name}` (");
            sql.AppendLine(string.Join(",\n", columns));

            if (primaryKeys.Any())
            {
                sql.AppendLine($",\nPRIMARY KEY ({string.Join(", ", primaryKeys)}) USING BTREE");
            }

            sql.AppendLine(") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;");

            _connection.Execute(sql.ToString());
            /*using (var command = _connection.CreateCommand())
            {
                command.CommandText = sql.ToString();
                command.ExecuteNonQuery();
            }*/
        }

        public int GetCount<T>(string where = "")
        {
            var type = typeof(T);
            string sql = $"SELECT COUNT(*) FROM `{type.Name}`";
            if (!string.IsNullOrEmpty(where))
            {
                sql += $" WHERE 1=1 {where}";
            }
            return _connection.ExecuteScalar<int>(sql);
        }



        //直接查全表
        //全表分页
        //根据主键查单记录
        //自定义sql
        //自定义sql分页


        /*public IEnumerable<T> DapperQuery<T>(string sql, object param = null)
        {

        }*/

        public IEnumerable<T> QueryList<T>(Expression<Func<T, bool>> expression)
        {
            var where = expression.ToSqlWhere();
            return QueryList<T>(where);
        }

        public IEnumerable<T> QueryList<T>(Expression<Func<T, bool>> expression, int pageSize, int page)
        {
            var where = expression.ToSqlWhere();
            return QueryList<T>(where, pageSize, page);
        }

        public IEnumerable<TResult> QueryJoin<T, TJoin, TResult>(
            Expression<Func<T, TJoin, bool>> joinExpression,
            Expression<Func<T, TJoin, TResult>> selectExpression,
            Expression<Func<T, TJoin, bool>> whereExpression = null)
        {
            var joinSql = joinExpression.ToJoinSql();
            var whereSql = whereExpression?.ToSqlWhere<T, TJoin>() ?? string.Empty;
            
            var type = typeof(T);
            var joinType = typeof(TJoin);
            
            string sql = $"SELECT * FROM {type.Name} t " +
                        $"JOIN {joinType.Name} j ON {joinSql}";
            
            if (!string.IsNullOrEmpty(whereSql))
            {
                sql += $" WHERE {whereSql}";
            }
            
            return _connection.Query<TResult>(sql);
        }

        public IEnumerable<TResult> QueryJoin<T, TJoin, TResult>(
            Expression<Func<T, TJoin, bool>> joinExpression,
            Expression<Func<T, TJoin, TResult>> selectExpression,
            Expression<Func<T, TJoin, bool>> whereExpression,
            int pageSize,
            int page)
        {
            var joinSql = joinExpression.ToJoinSql();
            var whereSql = whereExpression?.ToSqlWhere<T, TJoin>() ?? string.Empty;
            
            var type = typeof(T);
            var joinType = typeof(TJoin);
            
            string sql = $"SELECT * FROM {type.Name} t " +
                        $"JOIN {joinType.Name} j ON {joinSql}";
            
            if (!string.IsNullOrEmpty(whereSql))
            {
                sql += $" WHERE {whereSql}";
            }
            
            sql += $" LIMIT {(page - 1) * pageSize}, {pageSize}";
            
            return _connection.Query<TResult>(sql);
        }

        public void Update<T>(Expression<Func<T, T>> setExpression, Expression<Func<T, bool>> whereExpression, DbTransaction transaction = null)
        {
            var type = typeof(T);
            var (setClause, setParameters) = setExpression.ToUpdateSet();
            var whereClause = whereExpression.ToSqlWhere();
            
            var sql = $"UPDATE {type.Name} SET {setClause}";
            if (!string.IsNullOrEmpty(whereClause))
            {
                sql += $" WHERE {whereClause}";
            }

            if (transaction != null)
            {
                _connection.Execute(sql, setParameters, transaction);
            }
            else
            {
                _connection.Execute(sql, setParameters);
            }
        }

        public void Update<T>(Expression<Func<T, T>> setExpression, string id, string keyName = "Id", DbTransaction transaction = null)
        {
            var type = typeof(T);
            var (setClause, setParameters) = setExpression.ToUpdateSet();
            setParameters.Add($"@{keyName}", id);
            
            var sql = $"UPDATE {type.Name} SET {setClause} WHERE {keyName} = @{keyName}";

            if (transaction != null)
            {
                _connection.Execute(sql, setParameters, transaction);
            }
            else
            {
                _connection.Execute(sql, setParameters);
            }
        }

        public void BatchInsert<T>(IEnumerable<T> entities, DbTransaction transaction = null)
        {
            if (entities == null || !entities.Any())
                return;

            var type = typeof(T);
            var properties = type.GetProperties()
                .Where(p => p.CanWrite && p.PropertyType.IsPublic)
                .ToList();

            var columnNames = string.Join(", ", properties.Select(p => p.Name));
            var parameterNames = string.Join(", ", properties.Select(p => $"@{p.Name}"));
            var sql = $"INSERT INTO {type.Name} ({columnNames}) VALUES ({parameterNames})";

            if (transaction != null)
            {
                foreach (var entity in entities)
                {
                    var parameters = new DynamicParameters();
                    foreach (var property in properties)
                    {
                        parameters.Add("@" + property.Name, property.GetValue(entity, null));
                    }
                    _connection.Execute(sql, parameters, transaction);
                }
            }
            else
            {
                foreach (var entity in entities)
                {
                    var parameters = new DynamicParameters();
                    foreach (var property in properties)
                    {
                        parameters.Add("@" + property.Name, property.GetValue(entity, null));
                    }
                    _connection.Execute(sql, parameters);
                }
            }
        }

        public void BatchUpdate<T>(IEnumerable<T> entities, Expression<Func<T, T>> setExpression, Expression<Func<T, bool>> whereExpression, DbTransaction transaction = null)
        {
            if (entities == null || !entities.Any())
                return;

            var type = typeof(T);
            var (setClause, _) = setExpression.ToUpdateSet();
            var whereClause = whereExpression.ToSqlWhere();
            
            var sql = $"UPDATE {type.Name} SET {setClause}";
            if (!string.IsNullOrEmpty(whereClause))
            {
                sql += $" WHERE {whereClause}";
            }

            if (transaction != null)
            {
                foreach (var entity in entities)
                {
                    var parameters = new DynamicParameters();
                    var properties = type.GetProperties()
                        .Where(p => p.CanRead && p.PropertyType.IsPublic)
                        .ToList();

                    foreach (var property in properties)
                    {
                        parameters.Add("@" + property.Name, property.GetValue(entity, null));
                    }
                    _connection.Execute(sql, parameters, transaction);
                }
            }
            else
            {
                foreach (var entity in entities)
                {
                    var parameters = new DynamicParameters();
                    var properties = type.GetProperties()
                        .Where(p => p.CanRead && p.PropertyType.IsPublic)
                        .ToList();

                    foreach (var property in properties)
                    {
                        parameters.Add("@" + property.Name, property.GetValue(entity, null));
                    }
                    _connection.Execute(sql, parameters);
                }
            }
        }

        public void BatchUpdate<T>(IEnumerable<T> entities, string keyName = "Id", DbTransaction transaction = null)
        {
            if (entities == null || !entities.Any())
                return;

            var type = typeof(T);
            var properties = type.GetProperties()
                .Where(p => p.CanRead && p.PropertyType.IsPublic && !p.Name.Equals(keyName, StringComparison.OrdinalIgnoreCase))
                .ToList();

            var setClause = string.Join(", ", properties.Select(p => $"{p.Name} = @{p.Name}"));
            var sql = $"UPDATE {type.Name} SET {setClause} WHERE {keyName} = @{keyName}";

            if (transaction != null)
            {
                foreach (var entity in entities)
                {
                    var parameters = new DynamicParameters();
                    foreach (var property in properties)
                    {
                        parameters.Add("@" + property.Name, property.GetValue(entity, null));
                    }
                    parameters.Add("@" + keyName, type.GetProperty(keyName).GetValue(entity, null));
                    _connection.Execute(sql, parameters, transaction);
                }
            }
            else
            {
                foreach (var entity in entities)
                {
                    var parameters = new DynamicParameters();
                    foreach (var property in properties)
                    {
                        parameters.Add("@" + property.Name, property.GetValue(entity, null));
                    }
                    parameters.Add("@" + keyName, type.GetProperty(keyName).GetValue(entity, null));
                    _connection.Execute(sql, parameters);
                }
            }
        }

    }
}
